﻿using Dapper;
using System;
using BCEntity.TMS.VehicleBusinessMapping;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.TMS.Transport;

namespace BCData.TMS.VehicleBusinessMapping
{
    /// <summary>
    /// 车辆与业务配置重写
    /// </summary>
    public class VehicleBusinessMappingOverwriteData : IVehicleBusinessMappingOverwriteData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM vehiclebusinessmappingoverwrite";

        private const string QUERY = @"SELECT A.VehicleBusinessMappingOverwriteId,A.CountyCode,A.AgentRecordId,A.CreateAdminId,A.CreateAdminName,A.CreateTime,
											A.UpdateTime,A.EditAdminId,A.EditAdminName,A.VehicleBusinessMappingId,A.VehicleTransportBusinessConfigId,
											A.TransportBusiness,A.VehicleTypeId,A.VehicleTypeName,A.VehicleTypeRemark,A.Enabled,A.ProvinceCode,A.CityCode,A.MaxWithCarPeople,
                                            A.VehicleCategoryReferenceId,A.VehicleCategory,A.VehicleCategoryName,
                                            D.ProvinceName,D.CityName,D.CountyName
										FROM VehicleBusinessMappingOverwrite A
										INNER JOIN VehicleTransportBusinessConfig B ON A.VehicleTransportBusinessConfigId=B.VehicleTransportBusinessConfigId
										INNER JOIN County D ON A.CountyCode=D.CountyCode";

        private const string QUERY_COUNT = @"SELECT COUNT(1)
                                            FROM VehicleBusinessMappingOverwrite A
										    INNER JOIN VehicleTransportBusinessConfig B ON A.VehicleTransportBusinessConfigId=B.VehicleTransportBusinessConfigId
										    INNER JOIN County D ON A.CountyCode=D.CountyCode";

        public VehicleBusinessMappingOverwriteData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        #region 检查车辆与业务配置重写是否存在
        public async Task<bool> Exists(int vehicleBusinessMappingOverwriteId)
        {
            string sql = string.Format(@"{0} WHERE VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", vehicleBusinessMappingOverwriteId, DbType.Int32);
            return await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> Exists(long agentRecordId, string countyCode, int vehicleBusinessMappingId)
        {
            string sql = string.Format(@"{0} WHERE VehicleBusinessMappingId=?VehicleBusinessMappingId AND AgentRecordId=?AgentRecordId AND CountyCode=?CountyCode LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            parameters.Add("?CountyCode", countyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleBusinessMappingId", vehicleBusinessMappingId, DbType.Int32);
            return await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> InUsed(int vehicleBusinessMappingId)
        {
            string sql = string.Format(@"{0} WHERE VehicleBusinessMappingId=?VehicleBusinessMappingId LIMIT 1", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingId", vehicleBusinessMappingId, DbType.Int32);
            return await this.databaseContext.Master.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 添加车辆与业务配置重写
        public async Task<VehicleBusinessMappingEntity> Add(VehicleBusinessMappingEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("vehiclebusinessmappingoverwrite", new string[] { "CountyCode", "AgentRecordId", "CreateAdminId", "CreateAdminName","VehicleTypeName",
                "VehicleBusinessMappingId", "VehicleTransportBusinessConfigId", "TransportBusiness", "VehicleTypeId", "VehicleTypeRemark", "ProvinceCode", "CityCode", "MaxWithCarPeople",
                "VehicleCategoryReferenceId", "VehicleCategory", "VehicleCategoryName"});
            sql = string.Format("{0} {1} WHERE A.VehicleBusinessMappingOverwriteId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", entity.CountyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?AgentRecordId", entity.AgentRecordId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminId", entity.CreateAdminId, DbType.Int64, size: 20);
            parameters.Add("?CreateAdminName", entity.CreateAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleBusinessMappingId", entity.VehicleBusinessMappingId, DbType.Int32, size: 11);
            parameters.Add("?VehicleTransportBusinessConfigId", entity.VehicleTransportBusinessConfigId, DbType.Int32, size: 11);
            parameters.Add("?TransportBusiness", entity.TransportBusiness.ToString(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleTypeId", entity.VehicleTypeId, DbType.Int32, size: 11);
            parameters.Add("?VehicleTypeRemark", entity.VehicleTypeRemark?.Trim(), DbType.String);
            parameters.Add("?Enabled", entity.Enabled, DbType.Boolean);
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?MaxWithCarPeople", entity.MaxWithCarPeople, DbType.Int32, size: 11);
            parameters.Add("?VehicleTypeName", entity.VehicleTypeName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleCategoryReferenceId", entity.VehicleCategoryReferenceId, DbType.Int64);
            parameters.Add("?VehicleCategory", entity.VehicleCategory, DbType.AnsiString);
            parameters.Add("?VehicleCategoryName", entity.VehicleCategoryName, DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<VehicleBusinessMappingEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改车辆与业务配置重写
        public async Task<VehicleBusinessMappingEntity> Update(VehicleBusinessMappingEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("vehiclebusinessmappingoverwrite", new string[] { "CountyCode", "AgentRecordId", "EditAdminId", "EditAdminName",
                "VehicleBusinessMappingId", "VehicleTransportBusinessConfigId", "TransportBusiness", "VehicleTypeId", "VehicleTypeRemark", "ProvinceCode","VehicleTypeName",
                "CityCode","MaxWithCarPeople", "VehicleCategoryReferenceId", "VehicleCategory", "VehicleCategoryName" }, new string[] { "VehicleBusinessMappingOverwriteId" });
            sql = string.Format("{0} {1} WHERE A.VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", entity.VehicleBusinessMappingOverwriteId, DbType.Int32, size: 11);
            parameters.Add("?CountyCode", entity.CountyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?AgentRecordId", entity.AgentRecordId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminId", entity.EditAdminId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", entity.EditAdminName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleBusinessMappingId", entity.VehicleBusinessMappingId, DbType.Int32, size: 11);
            parameters.Add("?VehicleTransportBusinessConfigId", entity.VehicleTransportBusinessConfigId, DbType.Int32, size: 11);
            parameters.Add("?TransportBusiness", entity.TransportBusiness.ToString(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleTypeId", entity.VehicleTypeId, DbType.Int32, size: 11);
            parameters.Add("?VehicleTypeRemark", entity.VehicleTypeRemark?.Trim(), DbType.String);
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?MaxWithCarPeople", entity.MaxWithCarPeople, DbType.Int32, size: 11);
            parameters.Add("?VehicleTypeName", entity.VehicleTypeName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?VehicleCategoryReferenceId", entity.VehicleCategoryReferenceId, DbType.Int64);
            parameters.Add("?VehicleCategory", entity.VehicleCategory, DbType.AnsiString);
            parameters.Add("?VehicleCategoryName", entity.VehicleCategoryName, DbType.AnsiString);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<VehicleBusinessMappingEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 删除车辆与业务配置重写
        public async Task<bool> Delete(int vehicleBusinessMappingOverwriteId)
        {
            string sql = @"DELETE FROM vehiclebusinessmappingoverwrite WHERE VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", vehicleBusinessMappingOverwriteId, DbType.Int32);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取车辆与业务配置重写
        public async Task<VehicleBusinessMappingEntity> GetEntity(int vehicleBusinessMappingOverwriteId)
        {
            string sql = string.Format(@"{0} WHERE A.VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", vehicleBusinessMappingOverwriteId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
        public async Task<VehicleBusinessMappingEntity> GetEntity(int vehicleBusinessMappingId, long agentRecordId)
        {
            string sql = string.Format(@"{0} WHERE A.VehicleBusinessMappingId=?VehicleBusinessMappingId AND A.AgentRecordId=?AgentRecordId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingId", vehicleBusinessMappingId, DbType.Int32);
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
        public async Task<VehicleBusinessMappingEntity> GetEntity(TransportBusinessType transportBusinessType, int vehicleTypeId, int? vehicleCategoryReferenceId, long agentRecordId)
        {
            string sql = string.Format(@"{0} WHERE A.TransportBusiness=?TransportBusiness AND A.VehicleTypeId=?VehicleTypeId AND A.AgentRecordId=?AgentRecordId", QUERY);

            DynamicParameters parameters = new DynamicParameters();
            if (vehicleCategoryReferenceId.HasValue)
            {
                sql = string.Format("{0} AND A.VehicleCategoryReferenceId=?VehicleCategoryReferenceId LIMIT 1", sql);
                parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId, DbType.Int32);
            }
            parameters.Add("?TransportBusiness", transportBusinessType.ToString(), DbType.AnsiString);
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
        #endregion

        #region 获取车辆与业务配置重写列表
        public async Task<IEnumerable<VehicleBusinessMappingEntity>> GetList(long agentRecordId)
        {
            string sql = string.Format(@"{0} WHERE A.Enabled=1 AND A.AgentRecordId=?AgentRecordId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            return await this.databaseContext.Master.QueryAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
        public async Task<IEnumerable<VehicleBusinessMappingEntity>> GetList(string countyCode)
        {
            string sql = string.Format(@"{0} WHERE A.CountyCode=?CountyCode", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.databaseContext.Master.QueryAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
        #endregion

        #region 获取车辆与业务配置重写分页
        public async Task<(IEnumerable<VehicleBusinessMappingEntity>, int)> Query(long? agentRecordId, string provinceCode, string cityCode, string countyCode, int? vehicleTransportBusinessConfigId, int? vehicleTypeId, bool? enabled, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("A.AgentRecordId=?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            if (vehicleTypeId.HasValue)
            {
                wheres.Add("VehicleTypeId=?VehcileTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId.Value, DbType.Int32);
            }
            if (vehicleTransportBusinessConfigId.HasValue)
            {
                wheres.Add("A.VehicleTransportBusinessConfigId=?VehicleTransportBusinessConfigId");
                parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId.Value, DbType.Int32);
            }
            if (enabled.HasValue)
            {
                wheres.Add("A.Enabled=?Enabled");
                parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; {6} {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "A.CreateTime" : "A." + sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC",
                  QUERY_COUNT);
            return await this.databaseContext.QueryMultipleAsync<VehicleBusinessMappingEntity, int>(sql, parameters);
        }
        #endregion

        #region 启用车辆与业务配置重写
        public async Task<bool> Enabled(int vehicleBusinessMappingOverwriteId, long adminId, string fullname)
        {
            string sql = @"UPDATE vehiclebusinessmappingoverwrite SET Enabled=1,EditAdminId=?EditAdminId,EditAdminName=?EditAdminName WHERE VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", vehicleBusinessMappingOverwriteId, DbType.Int32);
            parameters.Add("?EditAdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", fullname, DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用车辆与业务配置重写
        public async Task<bool> Disabled(int vehicleBusinessMappingOverwriteId, long adminId, string fullname)
        {
            string sql = @"UPDATE vehiclebusinessmappingoverwrite SET Enabled=0,EditAdminId=?EditAdminId,EditAdminName=?EditAdminName WHERE VehicleBusinessMappingOverwriteId=?VehicleBusinessMappingOverwriteId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleBusinessMappingOverwriteId", vehicleBusinessMappingOverwriteId, DbType.Int32);
            parameters.Add("?EditAdminId", adminId, DbType.Int64, size: 20);
            parameters.Add("?EditAdminName", fullname, DbType.AnsiString, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取司机端车辆列表
        public async Task<IEnumerable<VehicleBusinessMappingForAppEntity>> GetListForApp(string countyCode)
        {
            string sql = @" SELECT A.VehicleBusinessMappingOverwriteId,A.CountyCode,A.AgentRecordId,A.CreateAdminId,A.CreateAdminName,A.CreateTime,
								   A.UpdateTime,A.EditAdminId,A.EditAdminName,A.VehicleBusinessMappingId,A.VehicleTransportBusinessConfigId,
								   A.TransportBusiness,A.VehicleTypeId,A.VehicleTypeName,A.VehicleTypeRemark,A.Enabled,A.ProvinceCode,A.CityCode,A.MaxWithCarPeople,
                                   D.ProvinceName,D.CityName,D.CountyName,E.VehiclePicPath,E.VehicleLength,E.VehicleWidth,E.VehicleHeight 
								   FROM VehicleBusinessMappingOverwrite A
								   INNER JOIN VehicleTransportBusinessConfig B ON A.VehicleTransportBusinessConfigId=B.VehicleTransportBusinessConfigId
								   INNER JOIN County D ON A.CountyCode=D.CountyCode
                                   INNER JOIN VehicleType E ON A.VehicleTypeId=E.VehicleTypeId  WHERE A.CountyCode=?CountyCode";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.databaseContext.Master.QueryAsync<VehicleBusinessMappingForAppEntity>(sql, parameters);
        }
        #endregion

        public async Task<IEnumerable<VehicleBusinessMappingEntity>> GetList(long? agentRecordId, string countyCode, int vehicleTransportBusinessConfigId)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("A.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode = ?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add("A.VehicleTransportBusinessConfigId = ?VehicleTransportBusinessConfigId");
            parameters.Add("?VehicleTransportBusinessConfigId", vehicleTransportBusinessConfigId, DbType.Int64);
            wheres.Add("A.Enabled = ?Enabled");
            parameters.Add("?Enabled", true, DbType.Boolean);
            string sql = string.Format("{0} {1} GROUP BY A.VehicleTypeId", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
            return await databaseContext.Slave.QueryAsync<VehicleBusinessMappingEntity>(sql, parameters);
        }
    }
}



